package com.irdstudio.efp.limit.common.util;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.compress.archivers.dump.InvalidFormatException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.irdstudio.basic.framework.core.util.ReflectUtility;
import com.irdstudio.efp.limit.common.annotation.ExcelField;
import com.irdstudio.efp.limit.common.annotation.ExcelSheet;

/**
 * 
 * @ClassName ExcelExportUtil
 * @Description excel导出工具
 * @version 1.0.0
 */
public class ExcelExportUtil {
	private static Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

	/**
	 * 导出Excel对象
	 *
	 * @param sheetDataListArr
	 *            Excel数据
	 * @return
	 * @throws IllegalAccessException
	 */
	public static Workbook exportWorkbook(List<?>... sheetDataListArr) throws IllegalAccessException {
		if (sheetDataListArr == null || sheetDataListArr.length == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data array can not be empty.");
		}
		// book （HSSFWorkbook=2003/xls、XSSFWorkbook=2007/xlsx）
		Workbook workbook = new HSSFWorkbook();
		for (List<?> dataList : sheetDataListArr) {
			makeSheet(workbook, dataList);
		}
		return workbook;
	}

	/**
	 * 
	 * @Description 装载数据
	 * @param is
	 * @param sheetDataListArr
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
	 */
	public static Workbook exportWorkbook(InputStream is, int rowIndex, List<?>... sheetDataListArr) throws EncryptedDocumentException,
			InvalidFormatException, IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
		if (sheetDataListArr == null || sheetDataListArr.length == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data array can not be empty.");
		}
		Workbook workbook = WorkbookFactory.create(is);// 创建workbook
		Sheet sheet = workbook.getSheet("Sheet1");// 获取第一个sheet内容
		if (sheet == null) {
			return workbook;
		}
		for (List<?> dataList : sheetDataListArr) {
			setData(workbook, rowIndex, dataList);
		}
		return workbook;
	}

	private static void setData(Workbook workbook, int rowIndex, List<?> sheetDataList) {
		// data
		if (sheetDataList == null || sheetDataList.size() == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data can not be empty.");
		}
		// sheet
		Class<?> sheetClass = sheetDataList.get(0).getClass();
		ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
		String sheetName = sheetDataList.get(0).getClass().getSimpleName();
		if (excelSheet != null) {
			if (excelSheet.name() != null && excelSheet.name().trim().length() > 0) {
				sheetName = excelSheet.name().trim();
			}
		}
		Sheet existSheet = workbook.getSheet(sheetName);
		if (existSheet == null) {
			for (int i = 2; i <= 10000; i++) {
				String newSheetName = sheetName.concat(String.valueOf(i)); // avoid sheetName repetition
				existSheet = workbook.getSheet(newSheetName);
				if (existSheet == null) {
					existSheet = workbook.createSheet(sheetName);
					break;
				} else {
					continue;
				}
			}
		}
		Sheet sheet = existSheet;
		// sheet field
		List<Field> fields = new ArrayList<Field>();
		if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
			for (Field field : sheetClass.getDeclaredFields()) {
				if (Modifier.isStatic(field.getModifiers())) {
					continue;
				}
				fields.add(field);
			}
		}
		if (fields == null || fields.size() == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
		}
		int count = 0;
		for (int dataIndex = 0; dataIndex < sheetDataList.size(); dataIndex++) {
			Object rowData = sheetDataList.get(dataIndex);
			int curRow = rowIndex + dataIndex - 1;
			Row rowX = sheet.getRow(curRow);// 获取行
			if (rowX == null) {
				sheet.createRow(curRow);
				rowX = sheet.getRow(curRow);// 获取行
				rowX.setHeightInPoints(25);
			}
			// sheet.createRow(rowIndex);
			for (int i = 0; i < fields.size(); i++) {
				Field field = fields.get(i);
				Object fieldValue = ReflectUtility.getObjectFieldValue(rowData, field);
				String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
				Cell cellY = null;
				if (field.getType().toString().equals("class java.math.BigDecimal") || field.getType().toString().equals("int")
						|| field.getType().toString().equals("Integer")) {
					cellY = rowX.createCell(i, CellType.NUMERIC);
				} else {
					cellY = rowX.createCell(i, CellType.STRING);
				}
				cellY.setCellValue(fieldValueString);
			}
		}
		count = workbook.getSheet("Sheet1").getLastRowNum();
	}

	private static void makeSheet(Workbook workbook, List<?> sheetDataList) throws IllegalAccessException {
		if (sheetDataList == null || sheetDataList.size() == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data can not be empty.");
		}
		Class<?> sheetClass = sheetDataList.get(0).getClass();
		ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
		String sheetName = sheetDataList.get(0).getClass().getSimpleName();
		int headColorIndex = -1;
		if (excelSheet != null) {
			if (excelSheet.name() != null && excelSheet.name().trim().length() > 0) {
				sheetName = excelSheet.name().trim();
			}
			headColorIndex = 1;// excelSheet.headColor().getIndex();
		}
		Sheet existSheet = workbook.getSheet(sheetName);
		if (existSheet != null) {
			for (int i = 2; i <= 1000; i++) {
				String newSheetName = sheetName.concat(String.valueOf(i)); // avoid sheetName repetition
				existSheet = workbook.getSheet(newSheetName);
				if (existSheet == null) {
					sheetName = newSheetName;
					break;
				} else {
					continue;
				}
			}
		}
		Sheet sheet = workbook.createSheet(sheetName);
		List<Field> fields = new ArrayList<Field>();
		if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
			for (Field field : sheetClass.getDeclaredFields()) {
				if (Modifier.isStatic(field.getModifiers())) {
					continue;
				}
				fields.add(field);
			}
		}
		if (fields == null || fields.size() == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
		}
		CellStyle[] fieldDataStyleArr = new CellStyle[fields.size()];
		int[] fieldWidthArr = new int[fields.size()];
		Row headRow = sheet.createRow(0);
		for (int i = 0; i < fields.size(); i++) {
			Field field = fields.get(i);
			ExcelField excelField = field.getAnnotation(ExcelField.class);
			String fieldName = field.getName();
			int fieldWidth = 0;
			if (excelField != null) {
				if (excelField.name() != null && excelField.name().trim().length() > 0) {
					fieldName = excelField.name().trim();
				}
				fieldWidth = excelField.width();
			}
			fieldWidthArr[i] = fieldWidth;
			CellStyle fieldDataStyle = workbook.createCellStyle();
			fieldDataStyleArr[i] = fieldDataStyle;
			CellStyle headStyle = workbook.createCellStyle();
			headStyle.cloneStyleFrom(fieldDataStyle);
			if (headColorIndex > -1) {
				headStyle.setFillForegroundColor((short) headColorIndex);
				headStyle.setFillBackgroundColor((short) headColorIndex);
				headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			}
			Cell cellX = headRow.createCell(i, CellType.STRING);
			cellX.setCellStyle(headStyle);
			cellX.setCellValue(String.valueOf(fieldName));
		}
		for (int dataIndex = 0; dataIndex < sheetDataList.size(); dataIndex++) {
			int rowIndex = dataIndex + 1;
			Object rowData = sheetDataList.get(dataIndex);
			Row rowX = sheet.createRow(rowIndex);
			for (int i = 0; i < fields.size(); i++) {
				Field field = fields.get(i);
				Object fieldValue = ReflectUtility.getObjectFieldValue(rowData, field);
				String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
				Cell cellX = rowX.createCell(i, CellType.STRING);
				cellX.setCellValue(fieldValueString);
				cellX.setCellStyle(fieldDataStyleArr[i]);
			}
		}
		for (int i = 0; i < fields.size(); i++) {
			int fieldWidth = fieldWidthArr[i];
			if (fieldWidth > 0) {
				sheet.setColumnWidth(i, fieldWidth);
			} else {
				sheet.autoSizeColumn((short) i);
			}
		}
	}

	/**
	 * 导出Excel文件到磁盘
	 *
	 * @param filePath
	 * @param sheetDataListArr
	 *            数据，可变参数，如多个参数则代表导出多张Sheet
	 * @throws IllegalAccessException
	 */
	public static void exportToFile(String filePath, List<?>... sheetDataListArr) throws IllegalAccessException {
		Workbook workbook = exportWorkbook(sheetDataListArr);
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);
			fileOutputStream.flush();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} finally {
			try {
				if (fileOutputStream != null) {
					fileOutputStream.close();
				}
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 导出Excel字节数据
	 *
	 * @param sheetDataListArr
	 * @return
	 * @throws IllegalAccessException
	 */
	public static byte[] exportToBytes(List<?>... sheetDataListArr) {
		ByteArrayOutputStream byteArrayOutputStream = null;
		byte[] result = null;
		try {
			Workbook workbook = exportWorkbook(sheetDataListArr);
			byteArrayOutputStream = new ByteArrayOutputStream();
			workbook.write(byteArrayOutputStream);
			byteArrayOutputStream.flush();
			result = byteArrayOutputStream.toByteArray();
			return result;
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} finally {
			try {
				if (byteArrayOutputStream != null) {
					byteArrayOutputStream.close();
				}
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 
	 * @Description 将要导入的数据写入传入的excel中
	 * @param is
	 * @param sheetDataListArr
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
	 */
	public static byte[] exportToBytes(InputStream is, int rowIndex, List<?>... sheetDataListArr) throws EncryptedDocumentException,
			InvalidFormatException, IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
		Workbook workbook = exportWorkbook(is, rowIndex, sheetDataListArr);
		ByteArrayOutputStream byteArrayOutputStream = null;
		byte[] result = null;
		try {
			byteArrayOutputStream = new ByteArrayOutputStream();
			int count = workbook.getSheet("Sheet1").getLastRowNum();

			workbook.write(byteArrayOutputStream);
			byteArrayOutputStream.flush();
			result = byteArrayOutputStream.toByteArray();
			return result;
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} finally {
			try {
				if (byteArrayOutputStream != null) {
					byteArrayOutputStream.close();
				}
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new RuntimeException(e);
			}
		}
	}

}
